<?php

/**
 *
 * SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurrences`
 * @author Kam
 */
class Application_Model_DAO_Search extends My_DAO_Abstract {

    const DB_EVENT = 'Event';
    const DB_OFFER = 'Offer';
    const DB_CATALOG = 'Catalog';

    public function getSimpleEventSearch($keyword) {
        $colsToSearch = array('ei.name', 'ei.description', 'ep.name', 'ep.description');  // kolejność ma znaczenie przy sortowaniu
        $select = $this->buildEventSearch();
        $kw = addslashes($keyword);
        $tmp = array();

        foreach ($colsToSearch as $v) {
            $tmp[] = $v . ' LIKE "%' . $kw . '%"';
        }

        $expl = explode(' ', $keyword);
        foreach ($colsToSearch as $v) {
            foreach ($expl as $val) {
                $kw = addslashes(trim($val));
                $sql = $v . ' LIKE "%' . $kw . '%"';
                $tmp[] = $sql;
            }
        }

        $where = implode(' OR ', $tmp);
        $select->where($where);
        $select->reset(Zend_Db_Table_Select::ORDER);
        $order = array();
        foreach ($tmp as $val) {
            $order[] = new Zend_Db_Expr($val . ' DESC');
        }
        $select->order(array_merge($order, array()));

        return $select;
    }

    /**
     * @return Zend_Db_Table_Select
     */
    public function buildEventSearch() {
        $daoEvent = new Application_Model_DAO_Event();
        $select = $daoEvent->getAllEvents();
        $dateStart = date(My_Db_Table::DATE_PATTERN);
        $dateEnd = date(My_Db_Table::DATE_PATTERN, time() + 3 * 24 * 60 * 60);
        $daoEvent->limitEventsByDateFrame($select, $dateStart, $dateEnd);
        return $select;
    }

    public function getSimpleCatalogSearch($keyword) {
        $colsToSearch = array('c.title', 'c.description', 'c.address', 'c.city', 'c.web');  // kolejność ma znaczenie przy sortowaniu
        $select = $this->buildCatalogSearch();
        $kw = addslashes($keyword);
        $tmp = array();

        foreach ($colsToSearch as $v) {
            $tmp[] = $v . ' LIKE "%' . $kw . '%"';
        }

        $expl = explode(' ', $keyword);
        foreach ($colsToSearch as $v) {
            foreach ($expl as $val) {
                $kw = addslashes(trim($val));
                $sql = $v . ' LIKE "%' . $kw . '%"';
                $tmp[] = $sql;
            }
        }

        $where = implode(' OR ', $tmp);
        $select->where($where);
        $select->reset(Zend_Db_Table_Select::ORDER);
        $order = array();
        foreach ($tmp as $val) {
            $order[] = new Zend_Db_Expr($val . ' DESC');
        }
        $select->order(array_merge($order, array('c.type DESC')));

        return $select;
    }

    public function getCatalogSearchByCategory($keyword, $categoryId) {
        $select = $this->getSimpleCatalogSearch($keyword);
        $daoCategory = new Application_Model_DAO_CatalogCategory();
        $subCategoriesIds = $daoCategory->getSubCategoriesIds($categoryId);
        $select->where('c.catalog_category_id IN (?)', array_merge(array($categoryId), $subCategoriesIds));
        return $select;
    }

    /**
     * @return Zend_Db_Table_Select
     */
    public function buildCatalogSearch() {
        $select = $this->getDbTable(self::DB_CATALOG)->selectActiveEntries();
        return $select;
    }

    public function getAdvancedOfferSearch($data) {
        $db = Zend_Db_Table::getDefaultAdapter();
        $tid = $data['tid'];
        $category = $db->select()->from('category')
                        ->where('offer_template_id = ?', $tid)
                        ->query()->fetch();
        if (empty($category)) {
            return array();
        }
        $cid = $category['category_id'];
        $keyword = $data['q'];
        
        $select = $db->select()->from(array('oa' => 'offer_attribute'), array('offer_id'))
                ->joinLeft(array('ota' => 'offer_template_attribute'), 'oa.offer_template_attribute_id=ota.offer_template_attribute_id', array())
                ->where('ota.offer_template_id = ?', $tid);
        $otaidsCnt = 0;
        $wheres = array();
        foreach ($data as $key => $value) {
            if (empty($value)) {
                continue;
            }
            if (strpos($key, 'otaid_') === false) {
                continue;
            }
            $otaid = str_replace('otaid_', '', $key);
            $wherekey = $this->quoteInto('oa.offer_template_attribute_id = ?', $otaid);
            $where = null;
            $number = str_replace(',', '.', $value);
            if (is_numeric($number)) {
                $number = floatval($number);
                $lo = $number * 0.8;
                $hi = $number * 1.2;
                $where = 'oa.value = ' . $number . ' OR (oa.value >= ' . $lo . ' AND oa.value <= ' . $hi . ')';
                // lub jeśli jest to wartość stringowa to zrobić LIKE
                $where .= $this->quoteInto(' OR (CONCAT("", oa.value * 1) != oa.value AND oa.value LIKE ?)', '%' . $value . '%');
            } elseif (is_array($value)) {
                if (isset($value['min']) && isset($value['max'])) {
                    $min = $value['min'];
                    $max = $value['max'];
                    $subWhere = array();
                    if (!empty($min)) {
                        $min = str_replace(',', '.', $min);
                        $subWhere[] = 'oa.value >= ' . $min;
                    }
                    if (!empty($max)) {
                        $max = str_replace(',', '.', $max);
                        $subWhere[] = 'oa.value <= ' . $max;
                    }
                    if (!empty($subWhere)) {
                        $where = implode(' AND ', $subWhere) . ' OR (CONCAT("", oa.value * 1) != oa.value)';
                    }
                }
            } else {
                $where = $this->quoteInto('oa.value LIKE ?', '%' . $value . '%');
            }
            if ($where != null) {
                $otaidsCnt++;
                $finalWhere = '(' . $wherekey . ' AND (' . $where . '))';
                $wheres[] = $finalWhere;
            }
        }
        if (empty($wheres)) {
            //kiedy brak zapytań do atrybutów to jest jak proste wyszukiwanie z uwzględnieniem kategorii
            $select = $this->getOfferSearchByCategory($keyword, $cid);
            return $select;
        }
        $select->where(implode(' OR ', $wheres));
//        d($select->assemble());
        $r = $select->query()->fetchAll();
        if (empty($r)) {
            return array();
        }
        $oids = array();
        foreach ($r as $row) {
            if (!isset($oids[$row['offer_id']])) {
                $oids[$row['offer_id']] = 0;
            }
            $oids[$row['offer_id']]++;
        }
        $oitsFinal = array();
        foreach ($oids as $id => $cnt) {
            if ($cnt >= $otaidsCnt) {
                $oitsFinal[] = $id;
            }
        }
        if (empty($oitsFinal)) {
            return array();
        }

        $select = $this->getOfferSearchByCategory($keyword, $cid);
        $select->where('o.offer_id IN (?)', $oitsFinal);
        return $select;
    }

    public function getSimpleOfferSearch($keyword) {
        $colsToSearch = array('o.title', 'o.content');  // kolejność ma znaczenie przy sortowaniu
        $select = $this->buildOfferSearch();
        $kw = addslashes($keyword);
        $tmp = array();

        foreach ($colsToSearch as $v) {
            $tmp[] = $v . ' LIKE "%' . $kw . '%"';
        }

        $expl = explode(' ', $keyword);
        foreach ($colsToSearch as $v) {
            foreach ($expl as $val) {
                $kw = addslashes(trim($val));
                $sql = $v . ' LIKE "%' . $kw . '%"';
                $tmp[] = $sql;
            }
        }

        $where = implode(' OR ', $tmp);
        $select->where($where);
        $select->reset(Zend_Db_Table_Select::ORDER);
        $order = array();
        foreach ($tmp as $val) {
            $order[] = new Zend_Db_Expr($val . ' DESC');
        }
        $select->order(array_merge($order, array('o.type DESC')));

        return $select;
    }

    public function getOfferSearchByCategory($keyword, $categoryId) {
        $select = $this->getSimpleOfferSearch($keyword);
        $daoCategory = new Application_Model_DAO_Category();
        $subCategoriesIds = $daoCategory->getSubCategoriesIds($categoryId);
        $select->where('o.category_id IN (?)', array_merge(array($categoryId), $subCategoriesIds));
        return $select;
    }

    /**
     * @return Zend_Db_Table_Select
     */
    public function buildOfferSearch() {
        $select = $this->getDbTable(self::DB_OFFER)->selectActiveOffers();
        return $select;
    }

    /**
     * @return Zend_Db_Adapter_Abstract
     */
    public static function getDB() {
        return Zend_Db_Table::getDefaultAdapter();
    }

    protected function getDbTableNameMap() {
        return array(
            self::DB_EVENT => 'Application_Model_DbTable_Event',
            self::DB_OFFER => 'Application_Model_DbTable_Offer',
            self::DB_CATALOG => 'Application_Model_DbTable_Catalog',
        );
    }

    protected function getDefaultDbTableName() {
        return self::DB_OFFER;
    }

}
